package com.search2.search;

import android.app.Application;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class SearchDbAdapter extends Application{
   
    public static final String QUERY = "query";
    public static final String dbPath = "/sdcard/";
    public static final String SEARCH_TYPE = "searchType";
    public static final String DEFAULT_FILE_NAME = "data.db";

    private SQLiteDatabase mDb;

    public boolean openDefault() {
    	try
    	{
    		mDb = SQLiteDatabase.openDatabase(dbPath + DEFAULT_FILE_NAME, null, SQLiteDatabase.OPEN_READONLY);
    		return true;
    	} catch (SQLException ex){
    		return false;
    	}
    }
    
    /*
     * Attempt to open the specified file, if OK then use this file
     */
    public boolean openFile(String fileName) {
    	try
    	{
    		mDb = SQLiteDatabase.openDatabase(dbPath + fileName, null, SQLiteDatabase.OPEN_READONLY);
    		//mDbFileName = fileName;
    		return true;
    	} catch (SQLException ex){
    		return false;
    	}
    }
    
    
    public void close()
    {
    	mDb.close();
    }
       
    public Cursor searchQuestions(String queryText) {    	
    	String searchQuestionsQuery = "select Title, content1, content2, content3, content4 FROM content WHERE Title LIKE ?";
    	String[] selectionArgs = {'%' + queryText + '%'};
    	
    	return mDb.rawQuery(searchQuestionsQuery, selectionArgs);
    }
    
    public Cursor searchAnswersWholeWord(String queryText) {    	
    	String searchAnswersQuery = "select Title, content1, content2, content3, content4 FROM content WHERE content1 LIKE ? OR content2 LIKE ? OR content3 LIKE ? OR content4 LIKE ?";    	
    	String[] selectionArgs = {'%' + queryText + '%','%' + queryText + '%','%' + queryText + '%','%' + queryText + '%'};
    	
    	return mDb.rawQuery(searchAnswersQuery, selectionArgs);
    }
    
    //Query for the first 3 letters only
    public Cursor searchAnswersFirstLetters(String queryText) {       	
    	//split the query into 3 parts
    	String part1, part2, part3;
    	int queryLength = queryText.length();
    	if(queryLength == 3) {
    		part1 = queryText.substring(0,1);
    		part2 = queryText.substring(1,2);
    		part3 = queryText.substring(2,3);
    	} else if (queryLength == 6) {
    		part1 = queryText.substring(0,2);
    		part2 = queryText.substring(2,4);
    		part3 = queryText.substring(4,6);
    	} else { //9
    		part1 = queryText.substring(0,3);
    		part2 = queryText.substring(3,6);
    		part3 = queryText.substring(6,9);	
    	}
    	
    	//TODO replace with stringbuilder?
    	String searchAnswersQuery = 
    		"select Title, content1, content2, content3, content4 FROM content" +  
    		" WHERE " +  
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" + 
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" + 
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " + 
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		" ((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))";   
    	
    	String[] selectionArgs = {
    		part1 + '%', part2 + '%', part3 + '%',
  			part1 + '%', part2 + '%', part3 + '%',
  			part1 + '%', part2 + '%', part3 + '%',
  		    part1 + '%', part2 + '%', part3 + '%',

  		    part1 + '%', part3 + '%', part2 + '%',
  		    part1 + '%', part3 + '%', part2 + '%',
  		    part1 + '%', part3 + '%', part2 + '%',
  		    part1 + '%', part3 + '%', part2 + '%',

  		    part2 + '%', part1 + '%', part3 + '%',
  		    part2 + '%', part1 + '%', part3 + '%',
  		    part2 + '%', part1 + '%', part3 + '%',
  		    part2 + '%', part1 + '%', part3 + '%',

  		    part2 + '%', part3 + '%', part1 + '%',
  		    part2 + '%', part3 + '%', part1 + '%',
  		    part2 + '%', part3 + '%', part1 + '%',
  		    part2 + '%', part3 + '%', part1 + '%',

  		    part3 + '%', part1 + '%', part2 + '%',
  		    part3 + '%', part1 + '%', part2 + '%',
  		    part3 + '%', part1 + '%', part2 + '%',
  		    part3 + '%', part1 + '%', part2 + '%',

  		    part3 + '%', part2 + '%', part1 + '%',
  		    part3 + '%', part2 + '%', part1 + '%',
  		    part3 + '%', part2 + '%', part1 + '%',
  		    part3 + '%', part2 + '%', part1 + '%'
  			};
    	
    	return mDb.rawQuery(searchAnswersQuery, selectionArgs);
    }
    
    //Query for the first 3 letters or last 3 letters only
    public Cursor searchAnswersFirstOrLastLetters(String queryText) { 
    	//split the query into 3 parts
    	String part1, part2, part3;
    	int queryLength = queryText.length();
    	if(queryLength == 3) {
    		part1 = queryText.substring(0,1);
    		part2 = queryText.substring(1,2);
    		part3 = queryText.substring(2,3);
    	} else if (queryLength == 6) {
    		part1 = queryText.substring(0,2);
    		part2 = queryText.substring(2,4);
    		part3 = queryText.substring(4,6);
    	} else { //9
    		part1 = queryText.substring(0,3);
    		part2 = queryText.substring(3,6);
    		part3 = queryText.substring(6,9);	
    	}
    	
    	//TODO replace with stringbuilder?
    	String searchAnswersQuery = 
    		"select Title, content1, content2, content3, content4 FROM content" +  
    		" WHERE " +  //start of first characters
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" + 
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" + 
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " + 
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		" ((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " + //start of last characters
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" + 
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" + 
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " + 
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		" ((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		"OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))" +
    		" OR " +
    		"((content2 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content3 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content4 LIKE ?)" +
    		" OR (content1 LIKE ? AND content2 LIKE ? AND content3 LIKE ?))"; 
    	
    	String[] selectionArgs = {
    		//start of first
    		part1 + '%', part2 + '%', part3 + '%',
  			part1 + '%', part2 + '%', part3 + '%',
  			part1 + '%', part2 + '%', part3 + '%',
  		    part1 + '%', part2 + '%', part3 + '%',

  		    part1 + '%', part3 + '%', part2 + '%',
  		    part1 + '%', part3 + '%', part2 + '%',
  		    part1 + '%', part3 + '%', part2 + '%',
  		    part1 + '%', part3 + '%', part2 + '%',

  		    part2 + '%', part1 + '%', part3 + '%',
  		    part2 + '%', part1 + '%', part3 + '%',
  		    part2 + '%', part1 + '%', part3 + '%',
  		    part2 + '%', part1 + '%', part3 + '%',

  		    part2 + '%', part3 + '%', part1 + '%',
  		    part2 + '%', part3 + '%', part1 + '%',
  		    part2 + '%', part3 + '%', part1 + '%',
  		    part2 + '%', part3 + '%', part1 + '%',

  		    part3 + '%', part1 + '%', part2 + '%',
  		    part3 + '%', part1 + '%', part2 + '%',
  		    part3 + '%', part1 + '%', part2 + '%',
  		    part3 + '%', part1 + '%', part2 + '%',

  		    part3 + '%', part2 + '%', part1 + '%',
  		    part3 + '%', part2 + '%', part1 + '%',
  		    part3 + '%', part2 + '%', part1 + '%',
  		    part3 + '%', part2 + '%', part1 + '%',
  		    //start of last
			'%' + part1, '%' + part2, '%' + part3,
			'%' + part1, '%' + part2, '%' + part3,
			'%' + part1, '%' + part2, '%' + part3,
		    '%' + part1, '%' + part2, '%' + part3,

		    '%' + part1, '%' + part3, '%' + part2,
		    '%' + part1, '%' + part3, '%' + part2,
		    '%' + part1, '%' + part3, '%' + part2,
		    '%' + part1, '%' + part3, '%' + part2,

		    '%' + part2, '%' + part1, '%' + part3,
		    '%' + part2, '%' + part1, '%' + part3,
		    '%' + part2, '%' + part1, '%' + part3,
		    '%' + part2, '%' + part1, '%' + part3,

		    '%' + part2, '%' + part3, '%' + part1,
		    '%' + part2, '%' + part3, '%' + part1,
		    '%' + part2, '%' + part3, '%' + part1,
		    '%' + part2, '%' + part3, '%' + part1,

		    '%' + part3, '%' + part1, '%' + part2,
		    '%' + part3, '%' + part1, '%' + part2,
		    '%' + part3, '%' + part1, '%' + part2,
		    '%' + part3, '%' + part1, '%' + part2,

		    '%' + part3, '%' + part2, '%' + part1,
		    '%' + part3, '%' + part2, '%' + part1,
		    '%' + part3, '%' + part2, '%' + part1,
		    '%' + part3, '%' + part2, '%' + part1
  			};
    	
    	return mDb.rawQuery(searchAnswersQuery, selectionArgs);
    }
    
    public Cursor searchAnswers(String queryText) {        	
    	String searchAnswersQuery = "SELECT Title, content1, content2, content3, content4 FROM CONTENT,ITEM WHERE ITEM.Word LIKE ? AND ITEM.REF_ID == CONTENT._ID";    	
    	String[] selectionArgs = {'%' + queryText + '%'};
    	
    	return mDb.rawQuery(searchAnswersQuery, selectionArgs);

    }
    
}
